﻿/*
This script was created by Visual Studio on 25.01.2008 at 15:51.
Run this script on DatabaseP301 to make it the same as poke.p301.dbo.
Please back up your target database before running this script.
*/
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ARITHABORT ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
PRINT N'Creating [dbo].[vUks]'
GO
CREATE VIEW dbo.vUks
AS
SELECT     K_UKS, NPP_UKS, D_DZ, K_ZAN, K_ADR, K_RAB, K_INF_CENTR, C_PO, N_ORG_NAPRAVIL, K_SP, C_GOD_SP, F_DETI_NET, K_PRICH_UR, K_PRICH, 
                      LTRIM(STR(NPP_UKS)) + ' - ' + LTRIM(STR(YEAR(D_DZ))) AS NPP_UKSyaer
FROM         dbo.b_uks
GO
PRINT N'Creating [dbo].[vKpo]'
GO
CREATE VIEW dbo.vKpo
AS
SELECT     K_KPO, K_UKS, NPP_KPO, K_OBRASCH, F_DEN, F_KRIZIS, D_OBRASCH, V_OBRASCH, K_INF_CENTR, F_KLIENT, F_KONS, K_SPEC, K_HELP_ORG, 
                      N_HELP, K_SP, F_DETI, K_UP, K_OBR, K_SOCSTAT, K_RAB, K_PRICH, K_KONS, N_FA, N_IM, N_OT, K_POL, K_POM, N_PRIM, GOD_R, K_ADR, 
                      N_PRICH, ISNULL(D_KONS, CASE WHEN F_KONS = 1 THEN D_OBRASCH END) AS D_KONS, ISNULL(D_KONS, 
                      CASE WHEN F_KONS = 1 THEN V_OBRASCH END) AS V_KONS, LTRIM(STR(NPP_KPO)) + ' - ' + LTRIM(STR(YEAR(D_OBRASCH))) AS NPP_KPOyear, 
                      dbo.N_FIO(N_FA, N_IM, N_OT) AS N_FIO
FROM         dbo.b_kpo
GO
PRINT N'Creating [dbo].[GroupKonsMake]'
GO
CREATE PROCEDURE dbo.GroupKonsMake
	(
		@K_GROUP int,
		@K_KLIENT int,
		@Date char(20),
		@TimeObrsach char(20),
		@TimeKons char(20),
		@K_UKS int
	)
-- Создание КПО на основании КПО и данных групповой консультации
AS

    declare @K_RAB  int 
    declare @K_SPEC int
    declare	@K_ZAN_GROUP int
    declare @N_PROBLEM nvarchar(1024)
    declare @N_MERI nvarchar(1024)
       
    -- Определение параметров консультации
    select top 1 @K_RAB = K_RAB, @K_SPEC = K_KONSULTANT, @K_ZAN_GROUP = K_ZAN, @N_PROBLEM = N_PROBLEM, @N_MERI = N_MERI
    from b_group where K_GROUP = @K_GROUP

	declare @K_PRIZN	int	-- Признак консульатации на жернале обращения
    declare @K_POM		int -- Код типа помощи на консультации УКС
    declare @K_KONS     int -- Код проведенной консультации УКС
    declare @K_ZAN      int -- Код занятия на проведенной консультации УКС

    if @K_ZAN_GROUP = 1			-- Групповая работы
    begin
        set @K_POM = 5
        set @K_PRIZN = 4
        set @K_ZAN = 1
        set @K_KONS = 5
    end
    else                    -- Кулбная работа
    begin
        set @K_POM = 10
        set @K_PRIZN = 5
        set @K_ZAN = 4
        set @K_KONS = 10
    end
    
	-- Создание новой КПО на основании данных клиента и УКС
	INSERT INTO b_kpo
						  (K_POL, GOD_R, K_OBR, K_SOCSTAT, K_RAB, K_ADR, K_INF_CENTR, K_SP, K_PRICH, K_UKS, K_SPEC, D_OBRASCH, V_OBRASCH, D_KONS, 
						  V_KONS, K_OBRASCH, N_FA, N_IM, N_OT)
	SELECT     b_klient.K_POL, b_klient.GOD_R, b_klient.K_OBR, b_klient.K_SOCSTAT, @K_RAB AS K_RAB, b_uks.K_ADR, b_uks.K_INF_CENTR, b_uks.K_SP, 
						  b_uks.K_PRICH, b_uks.K_UKS, @K_SPEC AS K_SPEC, @Date AS DateOblash, @TimeObrsach AS TimeObrsach, @Date AS DateKons, 
						  @TimeKons AS TimeKons, 1 AS K_OBRASCH, N_FA, N_IM, N_OT
	FROM         b_uks INNER JOIN
						  b_klient ON b_uks.K_UKS = b_klient.K_UKS INNER JOIN
						  b_group INNER JOIN
						  b_group_klient ON b_group.K_GROUP = b_group_klient.K_GROUP ON b_klient.K_KLIENT = b_group_klient.K_KLIENT
	WHERE     b_uks.K_UKS = @K_UKS and b_group.K_GROUP = @K_GROUP
		
	declare @newK_KPO int
	declare @lastK_KPO int
	set @newK_KPO  = (SELECT SCOPE_IDENTITY())
	
	-- получаем номер последней предыдущей КПО и пользуя фамилии
	set @lastK_KPO = (SELECT     max(b_kpo.K_KPO)
	FROM         b_group_klient INNER JOIN
	                      b_klient ON b_group_klient.K_KLIENT = b_klient.K_KLIENT INNER JOIN
	                      b_uks ON b_klient.K_UKS = b_uks.K_UKS INNER JOIN
	                      b_kpo ON b_uks.K_UKS = b_kpo.K_UKS AND ISNULL(b_klient.N_IM, '') = ISNULL(b_kpo.N_IM, '') AND ISNULL(b_klient.N_OT, '') = ISNULL(b_kpo.N_OT, 
	                      '') AND ISNULL(b_klient.N_FA, '') = ISNULL(b_kpo.N_FA, '')
	WHERE     b_group_klient.K_KLIENT = @K_KLIENT)
 	
	-- запивываем инфу в sv_kpo_pom
	INSERT INTO sv_kpo_pom (K_KPO, K_POM) VALUES (@newK_KPO, @K_POM)
	
	-- получаем первую причину из последнего КПО 
	declare @lastK_PRICH int
	set @lastK_PRICH = (Select top 1 K_PRICH from sv_kpo_prich where K_KPO = @lastK_KPO)

	-- Добавляем причину и последней КПО
	INSERT INTO sv_kpo_prich (K_KPO, K_PRICH) VALUES (@newK_KPO ,@lastK_PRICH)
	                   
	-- Получаем код поля информации о семье из последней КПО
	declare @lastK_SOSTAV_SEMYI int
	set @lastK_SOSTAV_SEMYI = (Select top 1 K_SOSTAV_SEMYI from b_sostav_semyi_kpo where K_KPO = @lastK_KPO)
           
	-- Копируем информацию о семье  в b_sostav_semyi_uks
	INSERT INTO b_sostav_semyi_kpo (K_KPO, N_FA, N_IM, N_OT, GOD_R, C_LET, K_POL, K_RODSTV_OTN, N_PRIM )
	SELECT     @newK_KPO AS newK_KPO, N_FA, N_IM, N_OT, GOD_R, C_LET, K_POL, K_RODSTV_OTN, N_PRIM
	FROM         b_sostav_semyi_kpo
	WHERE     (K_KPO = @lastK_KPO)
	
	-- Обновляем запись КПО данными из предыдущей КПО
	UPDATE    b_kpo
	SET              K_UKS = K_UKS, K_POL = K_POL, GOD_R = GOD_R, K_SP = K_SP, F_DETI = F_DETI, K_UP = K_UP, K_OBR = K_OBR, K_SOCSTAT = K_SOCSTAT, 
	                      K_INF_CENTR = K_INF_CENTR, N_PRIM = N_PRIM, F_KLIENT = F_KLIENT, N_FA = N_FA, N_IM = N_IM, N_OT = N_OT
	WHERE     (K_KPO = @newK_KPO) AND (K_KPO = @lastK_KPO)

	--  Добавление инфы в b_uks_konst
	INSERT INTO b_uks_kons (K_UKS, D_KONS, K_KPO, K_KLIENT, K_RAB , N_PROB, N_MERY, K_KONS, K_ZAN ) 
	SELECT     b_kpo.K_UKS, b_kpo.D_KONS, b_kpo.K_KPO, b_klient.K_KLIENT, @K_SPEC AS K_RAB, @N_PROBLEM AS N_PROB, @N_MERI AS N_MERY, 
	                      @K_KONS AS K_KONS, @K_ZAN AS K_ZAN
	FROM         b_klient INNER JOIN
	                      b_kpo ON b_klient.K_UKS = b_kpo.K_UKS
	WHERE     (b_kpo.K_KPO = @newK_KPO) AND (b_klient.K_KLIENT = @K_KLIENT)
	
	-- Добавление групповых консультаций в журнал обращений
	INSERT INTO b_zhurnal_obrasch (N_FA, N_IM, N_OT, K_RAB, D_OBRASCH, V_OBRASCH, F_PROVEDENA, K_OBRASCH, D_KONS, V_KONS, K_KPO, K_PRIZN, K_RAB_ZAPOL)
	SELECT     b_klient.N_FA, b_klient.N_IM, b_klient.N_OT, b_group.K_KONSULTANT, @Date AS D_OBRASCH, @TimeObrsach AS V_OBRASCH, 1 AS F_PROVEDENA, 
	                      1 AS K_OBRASCH, @Date AS D_KONS, @TimeKons AS V_KONS, @newK_KPO AS K_KPO, @K_PRIZN AS K_PRIZN, @K_RAB AS K_RAB_ZAPOL
	FROM         b_group INNER JOIN
	                      b_group_klient ON b_group.K_GROUP = b_group_klient.K_GROUP INNER JOIN
	                      b_klient ON b_group_klient.K_KLIENT = b_klient.K_KLIENT
	WHERE     (b_group_klient.F_NE_PRISHOL = 0) AND (b_group.K_GROUP = @K_GROUP) AND (b_klient.K_KLIENT = @K_KLIENT);

	
	RETURN
GO
PRINT N'Creating extended properties'
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "b_kpo"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', N'dbo', 'VIEW', N'vKpo', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', 1, 'SCHEMA', N'dbo', 'VIEW', N'vKpo', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "b_uks"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 271
               Right = 208
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', N'dbo', 'VIEW', N'vUks', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', 1, 'SCHEMA', N'dbo', 'VIEW', N'vUks', NULL, NULL
GO
